Code
import pandas as pd
import sqlite3
import sql
from itables import init_notebook_modeGreenhouse gas emissions attributable to products—from food to sneakers to appliances—make up more than 75% of global emissions.
Cite: The Carbon Catalogue
The dataset, which is publicly availably on nature.com, stores product carbon footprints (PCFs) for various companies. PCFs are the greenhouse gas emissions attributable to a given product, measured in CO2e (carbon dioxide equivalent).
Our database contains one table, product_emissions, which looks at PCFs by product as well as the stage of production these emissions occured in.
| field | data_type |
|---|---|
| id | VARCHAR |
| year | INT |
| product_name | VARCHAR |
| company | VARCHAR |
| country | VARCHAR |
| industry_group | VARCHAR |
| weight_kg | NUMERIC |
| carbon_footprint_pcf | NUMERIC |
| upstream_percent_total_pcf | VARCHAR |
| operations_percent_total_pcf | VARCHAR |
| downstream_percent_total_pcf | VARCHAR |
First, let’s look at a small subset of the data: emissions reported by Coca-Cola. Coke is actually made up of multiple companies around the globe, so we’ll make sure our query returns data for any company name that starts with “Coca-Cola”. Coke used to report for every single different product it has, so we will limit the results to six.
product_emissions table where the company name begins with “Coca-Cola”, limiting to the first six results.| index | id | year | product_name | company | country | industry_group | weight_kg | carbon_footprint_pcf | upstream_percent_total_pcf | operations_percent_total_pcf | downstream_percent_total_pcf |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 468 | 22710-1-2014 | 2014 | Coca-Cola (all packaging and sizes) | Coca-Cola HBC AG | Switzerland | Food, Beverage & Tobacco | 1.093 | 0.1673 | 84.28% | 11.12% | 4.60% |
| 469 | 22710-1-2015 | 2015 | Coca-Cola (all packaging and sizes) | Coca-Cola HBC AG | Switzerland | Food & Beverage Processing | 1.093 | 0.158 | 38.37% | 12.71% | 48.93% |
| 470 | 22710-1-2016 | 2016 | Coca-Cola (all packaging and sizes) | Coca-Cola HBC AG | Switzerland | Food, Beverage & Tobacco | 1.093 | 0.147 | 42.17% | 10.98% | 46.85% |
| 588 | 3565-10-2013 | 2013 | Coke Zero 330 ml glass bottle | Coca-Cola Enterprises, Inc. | USA | Food, Beverage & Tobacco | 0.541 | 0.34 | 73.95% | 3.42% | 22.63% |
| 589 | 3565-11-2013 | 2013 | Coke Zero 500ml PET | Coca-Cola Enterprises, Inc. | USA | Food, Beverage & Tobacco | 0.541 | 0.22 | 52.09% | 12.32% | 35.59% |
| 590 | 3565-1-2013 | 2013 | Coca-Cola 300ml can | Coca-Cola Enterprises, Inc. | USA | Food, Beverage & Tobacco | 0.3 | 0.17 | 69.76% | 7.00% | 23.24% |
We’ll focus on recent emissions data during this code-along. When was the most recent data collected?
What are the industries with the most emissions in 2017 (the most recent year that data is available)?
industry_group and a rounded total of carbon_footprint_pcf for each industry, aliasing as total_industry_footprint.total_industry_footprint.%%sql
-- Return industry_group and a rounded total of carbon_footprint_pcf, aliased as total_industry_footprint
-- Limit to data for 2017 and order by total_industry_footprint
SELECT industry_group, ROUND(SUM(carbon_footprint_pcf), 2) AS total_industry_footprint
FROM product_emissions
WHERE year = 2017
GROUP BY industry_group
ORDER BY SUM(carbon_footprint_pcf) DESC;| industry_group | total_industry_footprint |
|---|---|
| Materials | 107129.0 |
| Capital Goods | 94942.67 |
| Technology Hardware & Equipment | 21865.09 |
| Food, Beverage & Tobacco | 3161.47 |
| Commercial & Professional Services | 740.6 |
| Software & Services | 690.0 |
It looks like the Materials industry had a huge carbon footprint in 2017. But what if that’s just because there are many companies from the Materials industry in the dataset? Let’s check which industries are most heavily represented in that year.
industry_group included in the table and a count of the number of records that list that industry group.count_industry.count_industry, descending.%%sql
-- Return the industry groups and a count of the number of records for each group
-- Limit the results to only those from 2017 and alias the count as count_industry
-- Order by count_industry, descending
SELECT industry_group, COUNT(*) AS count_industry
FROM product_emissions
WHERE year = 2017
GROUP BY industry_group
ORDER BY COUNT(industry_group) DESC;| industry_group | count_industry |
|---|---|
| Technology Hardware & Equipment | 22 |
| Food, Beverage & Tobacco | 22 |
| Materials | 11 |
| Capital Goods | 4 |
| Commercial & Professional Services | 2 |
| Software & Services | 1 |
We can see that the Materials industry is the biggest emitter despite having less representation in our dataset than several other industries—yikes! The Capital Goods industry looks similar. Let’s explore the companies and products reporting for 2017 in the Capital Goods industry.
industry_group, company, and product_name for all records reporting in the Capital Goods industry during 2017.| industry_group | company | product_name |
|---|---|---|
| Capital Goods | Mitsui Mining & Smelting Co., Ltd. | Zinc Oxide |
| Capital Goods | Daikin Industries, Ltd. | Residential Air Conditioner |
| Capital Goods | Daikin Industries, Ltd. | Commercial Air Conditioner |
| Capital Goods | Daikin Industries, Ltd. | Light commercial Air Conditioner |
Daikin is an air conditioning and refrigeration manufacturer. Let’s look at emissions throughout the life cycle of Daikin products. Can you guess whether most emissions are upstream, downstream, or during operations?
product_name, company, upstream_percent_total_pcf, operations_percent_total_pcf, and downstream_percent_total_pcf for Daikin Industries, Ltd. in 2017.| product_name | company | upstream_percent_total_pcf | operations_percent_total_pcf | downstream_percent_total_pcf |
|---|---|---|---|---|
| Residential Air Conditioner | Daikin Industries, Ltd. | 3.96% | 0.55% | 95.50% |
| Commercial Air Conditioner | Daikin Industries, Ltd. | 0.98% | 0.13% | 98.88% |
| Light commercial Air Conditioner | Daikin Industries, Ltd. | 0.70% | 0.10% | 99.21% |
Let’s do some quick visualizations with plotly.express, a free an open-source graphing library. We don’t assume any knowledge of plotly on your part!
Let’s take a look at emissions by country. You may have noticed that each time we run a query, the query results are available as df. So if we were to select all information from product_emissions, that would create a DataFrame called df that we can use in a plotly visualization! Let’s try it.
product_emissions.| country | count |
|---|---|
| USA | 305 |
| Japan | 110 |
| Germany | 67 |
| Taiwan | 60 |
| Netherlands | 35 |
| Finland | 35 |
| United Kingdom | 32 |
| Switzerland | 28 |
| Sweden | 26 |
| Italy | 23 |
Let’s look at country representation in our dataset by creating a bar chart using the country column of df.
| country | count |
|---|---|
Loading ITables v2.2.4 from the init_notebook_mode cell...
(need help?) |
How does this compare to the emissions of companies reporting from each country?
country and the sum of total carbon_footprint_pcf by country, aliasing as total_country_footprint.%%sql --save country_footprint
-- Group by country
-- Select country and the sum of total carbon_footprint_pcf by country, aliasing as total_country_footprint
SELECT country, SUM(carbon_footprint_pcf) AS total_country_footprint
FROM product_emissions
GROUP BY country
ORDER BY total_country_footprint DESC;| country | total_country_footprint |
|---|---|
| Spain | 9786126.23 |
| Germany | 2251224.92482 |
| Japan | 519344.3134 |
| USA | 451869.1689 |
| Brazil | 167587.74467 |
| Luxembourg | 167007.3 |
| South Korea | 140994.148 |
| Netherlands | 70415.34 |
| Taiwan | 61513.068 |
| India | 24574.0 |
| country | total_country_footprint |
|---|---|
Loading ITables v2.2.4 from the init_notebook_mode cell...
(need help?) |
Wow! Spain has a lot of emissions! Where do they come from? To finish our exploration together, let’s take a quick look at the underlying data as a gut-check.
company and carbon_footprint_pcf for companies in Spain.| company | carbon_footprint_pcf |
|---|---|
| Gamesa Corporación Tecnológica, S.A. | 3718044.0 |
| Gamesa Corporación Tecnológica, S.A. | 3276187.0 |
| Gamesa Corporación Tecnológica, S.A. | 1532608.0 |
| Gamesa Corporación Tecnológica, S.A. | 1251625.0 |
| Compañía Española de Petróleos, S.A.U. CEPSA | 6109.0 |
| Compañía Española de Petróleos, S.A.U. CEPSA | 890.0 |
| Crimidesa | 180.0 |
| Agraz | 155.78 |
| Agraz | 155.71 |
| Crimidesa | 140.0 |
Gamesa Corporación Tecnológica is actually a renewable energy company specializing in wind power! Why might it have such high emissions?
Gamesa Corporación Tecnológica, despite being a leading wind energy company, may still have high CO₂ emissions due to several factors. The production of wind turbines involves energy-intensive materials like steel and concrete, while transportation and installation rely on fossil fuel-powered ships, trucks, and cranes. Additionally, if its manufacturing facilities use electricity from fossil fuel-based grids, indirect emissions can be significant. Maintenance, especially for offshore wind farms, often requires diesel-powered vehicles or helicopters, further adding to emissions. Lastly, the decommissioning and recycling of turbine components, particularly composite blades, is an energy-intensive process. However, despite these emissions, wind power still has a much lower carbon footprint compared to fossil fuel-based energy sources.